# -*- coding:utf-8 -*-

from sqlalchemy import select, update, delete, and_, or_, desc
from sqlalchemy.ext.asyncio import AsyncSession
from module_admin.entity.do.client_do import AgentClient
from module_admin.entity.vo.agent_client_vo import AgentClientModel, AgentClientQueryModel
from typing import Optional, List
from datetime import datetime
from utils.log_util import logger


class AgentClientDao:
    """Agent客户端数据访问层"""
    
    @classmethod
    async def get_client_by_id(cls, db: AsyncSession, client_id: int) -> Optional[AgentClient]:
        """根据ID获取客户端"""
        query = select(AgentClient).where(
            AgentClient.id == client_id,
            AgentClient.del_flag == '0'
        )
        result = await db.execute(query)
        return result.scalars().first()
    
    @classmethod
    async def get_client_by_client_id(cls, db: AsyncSession, client_id: str) -> Optional[AgentClient]:
        """根据client_id获取客户端"""
        query = select(AgentClient).where(
            AgentClient.client_id == client_id,
            AgentClient.del_flag == '0'
        )
        result = await db.execute(query)
        return result.scalars().first()
    
    @classmethod
    async def get_client_list(
        cls,
        db: AsyncSession,
        query_params: AgentClientQueryModel,
        page_num: int = 1,
        page_size: int = 10,
        current_user_id: int = None,
        is_admin: bool = True
    ):
        """分页查询客户端列表（关联server_host以获取主机名称，支持权限过滤）
        
        Args:
            db: 数据库会话
            query_params: 查询参数
            page_num: 页码
            page_size: 每页数量
            current_user_id: 当前用户ID
            is_admin: 是否是管理员（role_id=1的超级管理员）
        """
        from module_admin.entity.do.server_host_do import ServerHost
        from sqlalchemy.orm import aliased
        import re
        
        conditions = [AgentClient.del_flag == '0']
        
        # 权限过滤：非管理员只能看到分配给自己的agent
        if not is_admin and current_user_id:
            # ✅ 简化：直接比较 assigned_user_id（1对1分配）
            conditions.append(
                AgentClient.assigned_user_id == current_user_id
            )
        
        if query_params.client_name:
            conditions.append(AgentClient.client_name.like(f'%{query_params.client_name}%'))
        if query_params.client_id:
            conditions.append(AgentClient.client_id == query_params.client_id)
        if query_params.client_type:
            conditions.append(AgentClient.client_type == query_params.client_type)
        if query_params.online_status:
            conditions.append(AgentClient.online_status == query_params.online_status)
        if query_params.login_status:
            conditions.append(AgentClient.login_status == query_params.login_status)
        if query_params.status:
            conditions.append(AgentClient.status == query_params.status)
        
        # 查询总数
        count_query = select(AgentClient).where(and_(*conditions))
        count_result = await db.execute(count_query)
        total = len(count_result.scalars().all())
        
        # 分页查询（关联server_host表，通过IP匹配获取host_name）
        # 使用outerjoin（LEFT OUTER JOIN），确保即使没有匹配的server_host也能返回agent_client数据
        # 优先匹配internal_ip（agent通常通过内网连接），其次匹配host_ip
        # 注意：这里不做分页，先获取所有数据，排序后再分页
        query = (
            select(AgentClient, ServerHost.host_name.label('server_host_name'))
            .outerjoin(
                ServerHost,
                and_(
                    or_(
                        AgentClient.ip_address == ServerHost.internal_ip,  # 优先匹配内网IP
                        AgentClient.ip_address == ServerHost.host_ip       # 其次匹配外网IP
                    ),
                    ServerHost.del_flag == '0',
                    ServerHost.status == '0'
                )
            )
            .where(and_(*conditions))
        )
        
        result = await db.execute(query)
        rows = result.all()
        
        # 自然排序函数
        def natural_sort_key(row):
            """生成自然排序的key"""
            host_name = row[1] or ''  # row[1] 是 server_host_name
            
            # 如果没有主机名，排到最后
            if not host_name:
                return (1, '', 0)  # (1表示无主机名，排在后面)
            
            # 使用正则表达式提取前缀和数字
            # 例如 "agent-10" -> ["agent-", "10"]
            parts = re.split(r'(\d+)', host_name)
            
            # 构建排序key：将数字部分转换为整数
            key_parts = []
            for part in parts:
                if part.isdigit():
                    key_parts.append((0, int(part)))  # 数字部分用整数排序
                else:
                    key_parts.append((1, part))  # 字符串部分用字符串排序
            
            return (0, tuple(key_parts))  # (0表示有主机名，排在前面)
        
        # 按主机名自然排序
        sorted_rows = sorted(rows, key=natural_sort_key)
        
        # ✅ 暴力过滤：直接过滤掉server_host_name为None的记录（修复用户筛选主机bug）
        # 虽然数据库字段不空，但outerjoin（LEFT OUTER JOIN）可能返回NULL的server_host_name
        # 当agent_client的ip_address无法匹配到server_host表中的任何记录时，server_host_name为None
        filtered_rows = [row for row in sorted_rows if row[1] is not None]
        
        # 更新总数（过滤后的数量）
        total = len(filtered_rows)
        
        # 分页
        start_idx = (page_num - 1) * page_size
        end_idx = start_idx + page_size
        paginated_rows = filtered_rows[start_idx:end_idx]
        
        # 将结果转换为(client, server_host_name)元组列表
        clients_with_host = [(row[0], row[1]) for row in paginated_rows]
        
        return clients_with_host, total
    
    @classmethod
    async def get_online_clients(cls, db: AsyncSession) -> List[AgentClient]:
        """获取所有在线客户端"""
        query = select(AgentClient).where(
            AgentClient.del_flag == '0',
            AgentClient.online_status == 'online',
            AgentClient.status == '0'
        ).order_by(desc(AgentClient.last_heartbeat))
        result = await db.execute(query)
        return result.scalars().all()
    
    @classmethod
    async def get_clients_by_type(cls, db: AsyncSession, client_type: str, require_login: bool = False) -> List[AgentClient]:
        """根据类型获取客户端列表
        
        Args:
            db: 数据库会话
            client_type: 客户端类型（search/order）
            require_login: 是否要求已登录（默认False，搜索客户端不一定需要登录）
        """
        conditions = [
            AgentClient.del_flag == '0',
            AgentClient.client_type == client_type,  # ✅ 精确匹配：只返回指定类型的客户端
            AgentClient.online_status == 'online',
            AgentClient.status == '0'
        ]
        # ✅ 只有下单客户端才要求已登录，搜索客户端不一定需要登录
        if require_login:
            conditions.append(AgentClient.login_status == 'logged_in')
        
        query = select(AgentClient).where(*conditions).order_by(desc(AgentClient.last_heartbeat))
        result = await db.execute(query)
        clients = result.scalars().all()
        
        # ✅ 调试：记录查询结果和SQL（帮助排查）
        client_info = [(c.client_id, c.client_type, c.online_status) for c in clients]
        logger.info(f"get_clients_by_type: 查询 client_type='{client_type}', require_login={require_login}, 找到 {len(clients)} 个客户端，详情={client_info}")
        
        # ✅ 严格验证：确保所有返回的客户端的client_type都匹配
        mismatched = [c for c in clients if c.client_type != client_type]
        if mismatched:
            logger.error(f"❌ 严重错误：get_clients_by_type 返回了 {len(mismatched)} 个不匹配的客户端！")
            logger.error(f"期望类型='{client_type}'，但返回了: {[(c.client_id, c.client_type) for c in mismatched]}")
            # 过滤掉不匹配的客户端
            clients = [c for c in clients if c.client_type == client_type]
            logger.warning(f"已过滤掉不匹配的客户端，剩余 {len(clients)} 个正确的客户端")
        
        return clients
    
    @classmethod
    async def create_client(cls, db: AsyncSession, client: AgentClient) -> AgentClient:
        """创建客户端"""
        db.add(client)
        await db.flush()
        await db.refresh(client)
        return client
    
    @classmethod
    async def update_client(cls, db: AsyncSession, client_id: int, update_data: dict):
        """更新客户端信息"""
        query = update(AgentClient).where(
            AgentClient.id == client_id,
            AgentClient.del_flag == '0'
        ).values(**update_data)
        await db.execute(query)
    
    @classmethod
    async def update_client_by_client_id(cls, db: AsyncSession, client_id: str, update_data: dict):
        """根据client_id更新客户端信息"""
        query = update(AgentClient).where(
            AgentClient.client_id == client_id,
            AgentClient.del_flag == '0'
        ).values(**update_data)
        await db.execute(query)
    
    @classmethod
    async def delete_client(cls, db: AsyncSession, client_id: int):
        """删除客户端（软删除）"""
        query = update(AgentClient).where(
            AgentClient.id == client_id
        ).values(del_flag='2')
        await db.execute(query)
    
    @classmethod
    async def update_heartbeat(cls, db: AsyncSession, client_id: str):
        """更新心跳时间"""
        query = update(AgentClient).where(
            AgentClient.client_id == client_id,
            AgentClient.del_flag == '0'
        ).values(
            last_heartbeat=datetime.now(),
            online_status='online'
        )
        await db.execute(query)
    
    @classmethod
    async def update_client_status(cls, db: AsyncSession, client_id: str, status_data: dict):
        """更新客户端状态信息"""
        from urllib.parse import unquote
        from utils.log_util import logger
        
        update_data = {
            'last_heartbeat': datetime.now()
        }
        
        # ✅ 优先使用 status_data 中的 online_status，如果没有则默认为 'online'（因为能收到状态更新说明客户端在线）
        if 'online_status' in status_data:
            update_data['online_status'] = status_data['online_status']
        elif 'online' in status_data:
            # 兼容 Agent 发送的 online (bool) 格式
            update_data['online_status'] = 'online' if status_data.get('online') else 'offline'
        else:
            # 默认在线（因为能收到状态更新说明客户端在线）
            update_data['online_status'] = 'online'
        
        # 更新各种状态
        if 'browser_status' in status_data:
            update_data['browser_status'] = status_data['browser_status']
        if 'login_status' in status_data:
            update_data['login_status'] = status_data['login_status']
        if 'work_status' in status_data:
            update_data['work_status'] = status_data['work_status']
        if 'account' in status_data:
            update_data['account'] = status_data['account']
        if 'hostname' in status_data:
            update_data['hostname'] = status_data['hostname']
        
        # 处理cookie和昵称
        if 'cookie' in status_data or 'cookies' in status_data:
            import json
            cookie_data = status_data.get('cookie') or status_data.get('cookies')
            if cookie_data:
                # 保存cookie JSON
                update_data['cookies'] = json.dumps(cookie_data, ensure_ascii=False)
                
                # 🔥 从cookie中提取tracknick作为昵称
                if isinstance(cookie_data, dict) and 'tracknick' in cookie_data:
                    try:
                        tracknick_raw = cookie_data['tracknick']
                        account_nickname = unquote(tracknick_raw)
                        update_data['account_nickname'] = account_nickname
                        logger.info(f"✅ 从cookie解析昵称: {client_id} -> {account_nickname}")
                    except Exception as e:
                        logger.warning(f"解析cookie中的tracknick失败: {e}")
        
        # 如果Agent端直接传了account_nickname，优先使用（向后兼容）
        if 'account_nickname' in status_data and status_data['account_nickname']:
            update_data['account_nickname'] = status_data['account_nickname']
            logger.info(f"✅ 使用Agent传来的昵称: {client_id} -> {status_data['account_nickname']}")
        if 'last_error' in status_data:
            update_data['last_error'] = status_data['last_error']
        if 'error_count' in status_data:
            update_data['error_count'] = status_data['error_count']
        
        query = update(AgentClient).where(
            AgentClient.client_id == client_id,
            AgentClient.del_flag == '0'
        ).values(**update_data)
        await db.execute(query)
    
    @classmethod
    async def assign_clients_to_users(
        cls,
        db: AsyncSession,
        client_ids: List[int],
        user_id: Optional[int] = None
    ):
        """分配agent给用户（1对1分配）
        
        Args:
            db: 数据库会话
            client_ids: 要分配的agent ID列表
            user_id: 分配的用户ID（单个用户），None表示取消分配（回归管理员管理）
        """
        query = update(AgentClient).where(
            AgentClient.id.in_(client_ids),
            AgentClient.del_flag == '0'
        ).values(assigned_user_id=user_id)
        
        await db.execute(query)
    
    @classmethod
    async def get_assigned_user_name(cls, db: AsyncSession, user_id: Optional[int]) -> Optional[str]:
        """根据用户ID获取用户名（1对1分配）
        
        Args:
            db: 数据库会话
            user_id: 用户ID（单个用户）
            
        Returns:
            用户名（优先使用昵称），如果user_id为None则返回None
        """
        if not user_id:
            return None
        
        try:
            from module_admin.entity.do.user_do import SysUser
            from utils.log_util import logger
            
            # 查询用户名（同时查询昵称）
            query = select(SysUser.user_id, SysUser.user_name, SysUser.nick_name).where(
                SysUser.user_id == user_id,
                SysUser.del_flag == '0'
            )
            result = await db.execute(query)
            row = result.first()
            
            if not row:
                return None
            
            # 优先使用昵称，如果没有则使用用户名
            user_id_val, user_name, nick_name = row
            display_name = nick_name if nick_name else user_name
            
            return display_name
        except Exception as e:
            from utils.log_util import logger
            logger.error(f"获取分配用户名失败: {e}", exc_info=True)
            return None

